CREATE DATABASE CHIASETHONGTIN
GO
-----------------------------------------------------
-----------------------------------------------------
USE CHIASETHONGTIN
GO
-----------------------------
-- CHEDOXEM
CREATE TABLE CHEDOXEM(
	ID_CDX VARCHAR(20),
	ID_DOITUONG VARCHAR(20)
	constraint pk_cdx primary key(ID_CDX)
)
-- POST
CREATE TABLE POST(
	ID_POST VARCHAR(20),	
	ID_POSTER VARCHAR(20),
	ID_CDX VARCHAR(20),
	NGAYTHANG DATETIME,
	NOIDUNG VARCHAR(MAX)		
	constraint pk_post primary key(ID_POST)
)
-- COMMENT
CREATE TABLE COMMENT(
	ID_COMMENT VARCHAR(20),	
	ID_POST VARCHAR(20),	
	ID_COMMENTATOR VARCHAR(20),
	NGAYTHANG DATETIME,
	NOIDUNG VARCHAR(1000)
	constraint pk_cmt primary key(ID_COMMENT)
)
-- NEWCOMMENT
CREATE TABLE NOTIFICATIONS(
	ID VARCHAR(20),
	ID_OBJECT VARCHAR(20),
	ID_PERSON VARCHAR(20),
	STYLE VARCHAR(20)
	constraint pk_ntf primary key(ID)
)
-- FOLLOWINGPOST
CREATE TABLE FOLLOWINGPOST(
	ID VARCHAR(20),
	ID_POST VARCHAR(20),
	ID_FOLLOWER VARCHAR(20)
	constraint pk_flp primary key(ID)
)
-- FRIEND
CREATE TABLE FRIEND(
	ID  VARCHAR(20),
	ID_FRIEND1 VARCHAR(20),
	ID_FRIEND2 VARCHAR(20)		
	constraint pk_friend primary key(ID)
)
-- HISTORY
CREATE TABLE HISTORY(
	ID_HISTORY  VARCHAR(20),
	ID_USER  VARCHAR(20),
	NGAYTHANG DATETIME		
	constraint pk_history primary key(ID_HISTORY)
)
-- Friend Requests
-- FRIEND
CREATE TABLE FRIENDREQUEST(
	ID  VARCHAR(20),
	ID_HOST VARCHAR(20),
	ID_FRIEND VARCHAR(20)	
	constraint pk_friendrequest primary key(ID)
)
-- LIKE
CREATE TABLE LIKE_PC(
	ID  VARCHAR(20),
	ID_PC VARCHAR(20),
	ID_LIKER VARCHAR(20),
	STYLE VARCHAR(20)	
	constraint pk_LPC primary key(ID)
)
-- NUMLIKEs
CREATE TABLE LIKES(
	ID_HOST VARCHAR(20),
	NUMB numeric(18, 0)
	constraint pk_likes primary key(ID_HOST)
)
-- DISLIKE
CREATE TABLE DISLIKE_PC(
	ID  VARCHAR(20),
	ID_PC VARCHAR(20),
	ID_DISLIKER VARCHAR(20),
	STYLE VARCHAR(20)
	constraint pk_DLPC primary key(ID)
)
-- NUMDISLIKEs
CREATE TABLE DISLIKES(
	ID_HOST VARCHAR(20),
	NUMB numeric(18, 0)
	constraint pk_dislikes primary key(ID_HOST)
)
-- Khoa ngoai 
ALTER TABLE POST ADD CONSTRAINT fk01_post FOREIGN KEY(ID_CDX) REFERENCES  CHEDOXEM(ID_CDX)
ALTER TABLE COMMENT ADD CONSTRAINT fk01_cmt FOREIGN KEY(ID_POST) REFERENCES POST(ID_POST)
ALTER TABLE LIKE_PC ADD CONSTRAINT fk01_lpc FOREIGN KEY(ID_PC) REFERENCES POST(ID_POST)
ALTER TABLE LIKE_PC ADD CONSTRAINT fk02_lpc FOREIGN KEY(ID_PC) REFERENCES COMMENT(ID_COMMENT)
ALTER TABLE DISLIKE_PC ADD CONSTRAINT fk01_dlpc FOREIGN KEY(ID_PC) REFERENCES POST(ID_POST)
ALTER TABLE DISLIKE_PC ADD CONSTRAINT fk02_dlpc FOREIGN KEY(ID_PC) REFERENCES COMMENT(ID_COMMENT)
ALTER TABLE NEWCOMMENT ADD CONSTRAINT fk01_ncmt FOREIGN KEY(ID_COMMENT) REFERENCES COMMENT(ID_COMMENT)
ALTER TABLE FOLLOWINGPOST ADD CONSTRAINT fk01_fp FOREIGN KEY(ID_POST) REFERENCES POST(ID_POST)


----

ALTER TABLE POST ADD CONSTRAINT fk02_post FOREIGN KEY(ID_POSTER) REFERENCES USERNAME(ID_USERNAME)
ALTER TABLE COMMENT ADD CONSTRAINT fk02_cmt FOREIGN KEY(ID_COMMENTATOR) REFERENCES USERNAME(ID_USERNAME)
ALTER TABLE FRIEND ADD CONSTRAINT fk01_friend FOREIGN KEY(ID_FRIEND) REFERENCES USERNAME(ID_USERNAME)
ALTER TABLE FRIEND ADD CONSTRAINT fk02_friend FOREIGN KEY(ID_HOST) REFERENCES USERNAME(ID_USERNAME)
ALTER TABLE LIKES ADD CONSTRAINT fk01_ls FOREIGN KEY(ID_HOST) REFERENCES USERNAME(ID_USERNAME)
ALTER TABLE DISLIKES ADD CONSTRAINT fk01_dls FOREIGN KEY(ID_HOST) REFERENCES USERNAME(ID_USERNAME)
ALTER TABLE HISTORY ADD CONSTRAINT fk01_history FOREIGN KEY(ID_USER) REFERENCES USERNAME(ID_USERNAME)

